package com.server;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.dao.UserInfo;
import com.dbserver.Condb;

public class UsersListService {
	public ArrayList<UserInfo> getUsers(int order,int pageNum,int pageSize)
	   {   
		ArrayList<UserInfo> usersList=new ArrayList<UserInfo>();
		 //处理结果集
	   	Condb condb=new Condb();
	   	int BeginRecord=(pageNum-1)*pageSize;
	    int EndRecord=pageNum*pageSize;
	   	String sql="select u.id,u.username,u.regtime,u.mobilephone,u.wxid,u.credit,count(u.id) orderCount from user u left join orderitem o on u.id=o.userid group by u.id";
	   	
		if(order==1)
		{
			sql+=" order by regtime desc";
		}
		if(order==2)
		{
			sql+=" order by regtime asc";
		}
		
        if(order==3)
        {
     	 sql+="order by orderCount desc";
        }
        if(order==4){
     	 sql+="order by orderCount asc";
		}
        sql+=" limit "+BeginRecord+","+EndRecord;
	   	ResultSet rs=condb.query(sql);
		try {
	
			while(rs.next())
			{   
				
				UserInfo userInfo=new UserInfo();
				userInfo.setId(rs.getInt("id"));
				userInfo.setUsername(rs.getString("username"));
				userInfo.setCredit(rs.getInt("credit"));
				userInfo.setMobilePhone(rs.getString("mobilephone"));
				userInfo.setWxid(rs.getString("wxid"));
				userInfo.setRegTime(rs.getDate("regtime"));
				usersList.add(userInfo);
			}
			rs.close();
			condb.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return usersList;
	   }
}
